﻿using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public class RevisitDAL
    {
        //新增回访时，查出多少任务供勾选。
        public object GetTaskInfoForRevisitPick(ref int rowtotal, DateTime taskdatebegin, DateTime taskdateend, string Station, int CallType, string eventDispatchCenterID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(EnumSqlAppnd.TZAlarmEventType()); //2020-12-02 zch
            sb.Append(@"
select 	
	tt.任务编码
	,isnull(p.姓名,'')  as 责任调度人姓名		
	,isnull(ts.名称,'')  as 分站名称	
	,isnull(tzat.名称,'')  as 车辆类型名称	 
	,isnull(tab.实际标识,'')  as 车辆	 	
	,isnull(tac.呼救电话,'') as  呼救电话
	,isnull(tac.联系人,'') as   联系人
	,isnull(tac.联系电话,'') as   联系电话
	,isnull(tac.患者姓名,'') as  患者姓名
	,isnull(tac.性别,'') as  性别
	,isnull(tac.年龄,'') as  年龄 
	,isnull(tac.主诉,'') as  主诉 
	,isnull(tac.现场地址,'') as  现场地址
	,isnull(tac.送往地点,'') as  送往地点 
	,isnull(tt.生成任务时刻,'') as  生成任务时刻
	,tt.司机,tt.医生,tt.护士,tt.担架工,tt.抢救员
into #temp
from TTask  tt
inner join TAcceptEvent tac on tac.事件编码 = tt.事件编码 and tac.受理序号=tt.受理序号 
inner join TAlarmEvent tae on tae.事件编码 = tt.事件编码 
left join TAmbulance  tab on tt.车辆编码 = tab.车辆编码   
left join TZAmbulanceType tzat on tzat.编码 = tab.车辆类型编码 
left join TStation ts on ts.编码 = tt.分站编码
left join TPerson p on p.编码  = tt.责任调度人编码 
where 1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("tt.生成任务时刻", taskdatebegin, sb);
            WhereClauseUtility.AddDateTimeLessThan("tt.生成任务时刻", taskdateend, sb);
            WhereClauseUtility.AddInSelectQuery("ts.编码", Station, sb);

            if (eventDispatchCenterID == "1")
                sb.Append(" and tt.中心编码<=6 ");
            else
                WhereClauseUtility.AddStringEqual("tt.中心编码", eventDispatchCenterID, sb); //2021-06-04 gdg
           
            if (CallType == 1) //判断手机还是固话
            {
                sb.Append(@"
and len(tac.联系电话)<11  ");
            }
            else if (CallType == 2)
            {
                sb.Append(@"    
and len(tac.联系电话)>=11  ");
            }
            sb.Append(@" 
and tt.是否正常结束=1 and tae.事件类型编码 =@救治 and tac.联系电话<>''
and tac.送往地点 like '%医院%'  and len(tac.送往地点)>2 ");
            //判断 任务编码不在 回访从表里的任务编码中
            SqlConnectionStringBuilder buildermain = new SqlConnectionStringBuilder(SqlHelper.MainConnectionString);
            sb.Append(@"  
and tt.任务编码 not in (select taskCode from ").Append(buildermain.InitialCatalog).Append(".dbo.I_RevistDetail rd where  rd.StartTaskTime >='" + taskdatebegin + "' and  rd.StartTaskTime <'" + taskdateend + @"' ) 
 
SELECT * FROM #temp t order by  分站名称,生成任务时刻  
SELECT count(*) FROM #temp t 
drop table #temp ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            List<TaskForRevisitPickInfo> list = new List<TaskForRevisitPickInfo>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                TaskForRevisitPickInfo info = new TaskForRevisitPickInfo();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    info = new TaskForRevisitPickInfo();
                    info.TaskCode = Convert.ToString(dr["任务编码"]);
                    info.DispatcherName = Convert.ToString(dr["责任调度人姓名"]);
                    info.StationName = Convert.ToString(dr["分站名称"]);
                    info.AmbulanceType = dr["车辆类型名称"].ToString();
                    info.RealAmbulanceSign = Convert.ToString(dr["车辆"]);
                    info.AlarmTel = dr["呼救电话"].ToString();
                    info.LinkMan = Convert.ToString(dr["联系人"]);
                    info.LinkCall = Convert.ToString(dr["联系电话"]);
                    info.PatientName = dr["患者姓名"].ToString();
                    info.Sex = dr["性别"].ToString();
                    info.Age = dr["年龄"].ToString();
                    info.AlarmReason = dr["主诉"].ToString();
                    info.LocalAddr = Convert.ToString(dr["现场地址"]);
                    info.SendAddr = Convert.ToString(dr["送往地点"]);
                    info.StartTaskTime = Convert.ToDateTime(dr["生成任务时刻"]);
                    info.Driver = Convert.ToString(dr["司机"]);
                    info.Doctor = Convert.ToString(dr["医生"]);
                    info.Stretcher = Convert.ToString(dr["担架工"]);
                    list.Add(info);
                }
            }

            else
            {
                list = null;
                rowtotal = 0;
                return list;
            }
            rowtotal = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return list;
        }

        //回访主表内容
        public object GetRevisitMain(int page, int rows, ref int rowtotal)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@" 
                select
                        identity(int,1,1) as 行号, 
                                ID
                      ,FillPerson
                      ,TaskDate
                      ,FillTime
                      ,LastModifier
                      ,isnull(LastModifyTime,'') as LastModifyTime
                      ,FillDepartment
                      ,RevisitDeclare
                      ,CompleteNumber
                      ,UnfinishedNumber
             , case when (CompleteNumber+UnfinishedNumber)<>0 then( cast(round((CompleteNumber *1.0/(CompleteNumber+UnfinishedNumber))*100,2)   as   numeric(10,2))   ) else 0 end as  CompleteRate 

                into #temp 
                from I_Revisit  order by  FillTime desc 

                ");

            sb.Append(@"select top " + rows + " A.*  from #temp A where 行号>" + (page - 1) * rows + " order by 行号 ");
            sb.Append(@"SELECT count(*) FROM #temp t ");
            sb.Append("drop table #temp ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            List<RevisitMainInfo> list = new List<RevisitMainInfo>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                RevisitMainInfo info = new RevisitMainInfo();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    info = new RevisitMainInfo();
                    info.FillPerson = Convert.ToString(dr["FillPerson"]);
                    info.FillDate = Convert.ToDateTime(dr["TaskDate"]);
                    info.ID = Convert.ToString(dr["ID"]);
                    info.FillTime = Convert.ToDateTime(dr["FillTime"]);
                    info.LastModifier = Convert.ToString(dr["LastModifier"]);
                    info.LastModifyTime = Convert.ToDateTime(dr["LastModifyTime"]);
                    info.FillDepartment = Convert.ToString(dr["FillDepartment"]);
                    info.RevisitDeclare = Convert.ToString(dr["RevisitDeclare"]);
                    info.CompleteNumber = Convert.ToInt16(dr["CompleteNumber"]);
                    info.UnfinishedNumber = Convert.ToInt16(dr["UnfinishedNumber"]);
                    info.CompleteRate = Convert.ToDouble(dr["CompleteRate"]);
                    list.Add(info);
                }
            }
            else
            {
                list = null;
                rowtotal = 0;
                return list;
            }
            rowtotal = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return list;
        }

        public object GetRevisitAddDetail(ref int rowtotal, string ID, int ifOperate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@" 
               select 
	                     taskCode
	                     ,RevisitState
                            ,RevisitWay ='电话' ");

            sb.Append(@" ,Satisfaction ");

            sb.Append(@"	                  
                         ,NotSatisfactionReson
	                     ,Station
	                     ,Ambluance
	                     ,AlarmTel
	                     ,LinkCall
	                     ,LinkMan
	                     ,LocalAddr
	                     ,PatientName
	                     ,Sex
	                     ,Age
	                     ,AlarmReason
	                     ,SendAddr
	                     ,StartTaskTime
	                     ,DispatcherName
	                     ,Driver
	                     ,Doctor
	                     ,Stretcher
	                     ,FillDate
	                     ,FillPerson
	                     ,isnull(RevistPerson,'') as RevistPerson
	                     ,isnull(RevistTime,'') as RevistTime
	                     ,rd.ID
                      into #temp 

	                     from I_RevistDetail  rd
	                    -- left join TDictionary tdrw on tdrw.ID = rd.RevisitWay
	                    -- left join TDictionary tds on tds.ID = rd.Satisfaction
	                   --  left join TDictionary tdns on tdns.ID = rd.NotSatisfactionReson
                        where rd.ID = '" + ID + @"'         ");

            if (ifOperate == 1)
            {
                sb.Append("  and  rd.RevisitState ='已回访'  ");

            }
            else if (ifOperate == 2) { sb.Append("  and  rd.RevisitState ='未回访'  "); }

            // sb.Append(@"select top " + rows + " A.*  from #temp A where 行号>" + (page - 1) * rows + " order by 行号 ");
            sb.Append(@"SELECT * FROM #temp t order by  StartTaskTime ");
            sb.Append(@"SELECT count(*) FROM #temp t ");
            sb.Append("drop table #temp ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            List<I_RevistDetail> list = new List<I_RevistDetail>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                I_RevistDetail info = new I_RevistDetail();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    info = new I_RevistDetail();
                    info.taskCode = Convert.ToString(dr["taskCode"]);
                    info.RevisitState = Convert.ToString(dr["RevisitState"]);
                    info.RevisitWay = Convert.ToString(dr["RevisitWay"]);
                    info.Satisfaction = Convert.ToString(dr["Satisfaction"]);
                    info.NotSatisfactionReson = Convert.ToString(dr["NotSatisfactionReson"]);
                    info.Station = Convert.ToString(dr["Station"]);
                    info.Ambluance = Convert.ToString(dr["Ambluance"]);
                    info.AlarmTel = Convert.ToString(dr["AlarmTel"]);
                    info.LinkCall = Convert.ToString(dr["LinkCall"]);
                    info.LinkMan = Convert.ToString(dr["LinkMan"]);
                    info.LocalAddr = Convert.ToString(dr["LocalAddr"]);
                    info.PatientName = Convert.ToString(dr["PatientName"]);
                    info.Sex = Convert.ToString(dr["Sex"]);
                    info.Age = Convert.ToString(dr["Age"]);
                    info.AlarmReason = Convert.ToString(dr["AlarmReason"]);
                    info.SendAddr = Convert.ToString(dr["SendAddr"]);
                    info.StartTaskTime = Convert.ToDateTime(dr["StartTaskTime"]);
                    info.DispatcherName = Convert.ToString(dr["DispatcherName"]);
                    info.Driver = Convert.ToString(dr["Driver"]);
                    info.Doctor = Convert.ToString(dr["Doctor"]);
                    info.Stretcher = Convert.ToString(dr["Stretcher"]);
                    info.FillDate = Convert.ToDateTime(dr["FillDate"]);
                    info.FillPerson = Convert.ToString(dr["FillPerson"]);
                    info.RevistPerson = Convert.ToString(dr["RevistPerson"]);
                    info.RevistTime = Convert.ToDateTime(dr["RevistTime"]);
                    info.ID = Convert.ToString(dr["ID"]);
                    list.Add(info);
                }
            }
            else
            {
                list = null;
                rowtotal = 0;
                return list;
            }
            rowtotal = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return list;
        }

        //回访数量，供操作回访保存时，修改主表完成未完成数量赋值使用
        public RevisitNumberInfo GetRevisitNumber(string ID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@" 
                       select  count(taskCode) as 回访任务, 
                      isnull( sum(case when  rd.RevisitState='已回访' then 1 else 0 end ),0) as 已回访,
                        isnull(sum(case when rd.RevisitState='未回访' then 1 else 0 end),0) as 未回访

                         from  I_RevistDetail    rd  
                        where rd.ID='" + ID + @"'  ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                RevisitNumberInfo AEinfo = new RevisitNumberInfo();
                if (dr.Read())
                {
                    AEinfo.RevistNumber = Convert.ToInt16(dr["回访任务"]);
                    AEinfo.CompleteNumber = Convert.ToInt16(dr["已回访"]);
                    AEinfo.UnfinishedNumber = Convert.ToInt16(dr["未回访"]);

                }
                return AEinfo;
            }
        }
        //获取老描述，用于党办更新主表时，更改描述内容
        public string getOldDeclare(string ID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@" 
                select                     
                     rv.RevisitDeclare 
                    from   I_Revisit  rv 
                    where rv.ID = '" + ID + @"' 
                    ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            string result = "";
            if (ds.Tables[0].Rows.Count > 0)
            {
                result = Convert.ToString(ds.Tables[0].Rows[0][0]);
            }
            return result;
        }


        //新增回访主表时，判断日期是否存在，如果存在，附表的ID 用取出来的ID 
        public List<I_Revisit> getRevisitAllDate()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
            select ID, TaskDate  from I_Revisit
            ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            List<I_Revisit> list = new List<I_Revisit>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                I_Revisit AEinfo = new I_Revisit();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    AEinfo.ID = Convert.ToString(dr["ID"]);
                    AEinfo.TaskDate = Convert.ToDateTime(dr["TaskDate"]);
                    list.Add(AEinfo);
                }
            }
            return list;
        }
    }
}
